﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Threading;
using System.Windows;
using System.Windows.Controls;
using System.Linq;
using Lite.ExcelLibrary.SpreadSheet;

namespace Lib.Helper
{
    public class ImportHelper
    {
        public ExcelClass excelclass;
        String current = null;
        List<Stream> streams = new List<Stream>();
        List<string> stringTable = new List<string>();
        IEnumerable<List<string>> returnedlist = new List<List<string>>();
        Stream getStyles = null;
        IEnumerable<List<string>> headers = new List<List<string>>();
        int getRowID = 0;
        bool loadingHeaders = false;
        public string name;

        public List<string> listSheetNames  { get; set; }
      
        public ImportHelper()
        {
            listSheetNames = new List<string>();
        }

        public void BrowseExcelFile(out IEnumerable<List<object>> sources)
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            getRowID = 1;
            listSheetNames.Clear();
            streams.Clear();
            Stream Wookbooknames = null;
            sources = default(IEnumerable<List<object>>);

            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "xlsx (*.xlsx) |*.xlsx | xls (*.xls) |*.xls";
                UnZipper unzip;

                if ((bool)dialog.ShowDialog())
                {
                    if (dialog.File.Extension == ".xls")
                    {
                        sources = ForExcelOldFile(dialog);
                        return;
                    }

                    Stream s = dialog.File.OpenRead();
                    unzip = new UnZipper(s);
                    Stream sharedStringsStream = s;
                    Stream WorkSheetStream = s;

                    foreach (string filename in unzip.GetFileNamesInZip())
                    {
                        Stream stream = unzip.GetFileStream(filename);
                        String filenameCompare = filename;
                        StreamReader reader = new StreamReader(stream);
                        String sharedStringsCompmare;
                        String findStyles;

                        findStyles = @"xl/styles.xml";

                        String findSheetsName = @"xl/workbook.xml";

                        sharedStringsCompmare = @"xl/sharedStrings.xml";

                        String filenamefind = @"xl/worksheets/";

                        if (filenameCompare.Contains(filenamefind) && filename.Contains("rels") == false)
                        {
                            streams.Add(stream);
                        }
                        if (filenameCompare == sharedStringsCompmare)
                        {
                            sharedStringsStream = stream;
                        }
                        if (filenameCompare == findStyles)
                        {
                            getStyles = stream;
                        }
                        if (filenameCompare == findSheetsName)
                        {
                            Wookbooknames = stream;

                        }
                    }

                    stringTable = ExcelClass.ReadStringTable(sharedStringsStream);

                    listSheetNames = ExcelClass.getWorkbookNames(Wookbooknames, streams.Count());

                    sharedStringsStream.Close();

                }

           
            }

            catch (IOException)
            {
                MessageBox.Show("File is in use - Please close the file and try again!");
            }
        }


        public IEnumerable<List<object>> ForExcelOldFile(OpenFileDialog fileDialog)
        {
           
            // Simply call the Open method of Workbook and you are done
            Workbook book = Workbook.Open(fileDialog.File.OpenRead());
            Worksheet sheet = book.Worksheets[0];
            string val =  string.Empty ;
            for (int i = sheet.Cells.FirstRowIndex; i <= sheet.Cells.LastRowIndex; i++)
            {
                List<object> res = new List<object>();
                for (int j = sheet.Cells.FirstColIndex; j <= sheet.Cells.LastColIndex; j++)
                {
                    res.Add(sheet.Cells[i, j].Value);
                }
                // New row will be displayed on next line

                  var tes =   Environment.NewLine;
                yield return res;
            }
        }


        public IEnumerable<IDictionary> GenerateData(IEnumerable<List<string>> data)
        {
            var key = headers.First();
            var values = headers.Last();

            //if (loadingHeaders == false)
            //{

            //    grid.Columns.Clear();

            //    for (int i = 0; i < key.Count(); i++)
            //    {
            //        grid.Columns.Add(
            //        new DataGridTextColumn
            //        {
            //            Header = values[i],
            //            Binding = new Binding(key[i])
            //        });

            //        loadingHeaders = true;
            //    }
            //}

            foreach (List<string> row in data)
            {
                var dict = new Dictionary<string, object>();

                for (int j = 0; j < key.Count(); j++)
                {
                    dict[key[j]] = row[j];
                }
                yield return dict;
            }

        }

        public IEnumerable LoadExcellFile(String fileName)
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            returnedlist = null;
            if (string.IsNullOrEmpty(fileName)) return default(IEnumerable);
            current = fileName;
            object[] returnlists = new object[2];
            int j = listSheetNames.Count() - 1;

            for (int i = 0; i < listSheetNames.Count(); i++)
            {
                if (listSheetNames[i] == current)
                {
                    returnlists = ExcelClass.ReadWorksheet(streams[j], stringTable, getStyles, getRowID);
                }
                else
                {
                    j--;
                }
            }
            returnedlist = (List<List<string>>)returnlists[0];
            headers = (List<List<string>>)returnlists[1];

            if (returnedlist.Count() == 0)
            {
                MessageBox.Show("Empty Workbook please select another");
                return default(IEnumerable);
            }
            else
            {
                return GenerateData(returnedlist).ToDataSource();
            }

        }
    }
}
